﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class DALBase
    {


        #region Propiedades
        /// <summary>
        /// String que conecta con el valor de la ConnectionString que vamos a utilizar.
        /// Ésta se ubica en el Web.config Es importante ponerla en el Web.config porque
        /// de ese modo prodrá ser accedida desde cualquier sitio, no sólo de aquí
        /// </summary>
        protected static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["conAlex"].ConnectionString;
        }

 


        #endregion


        #region EjecutaNonQuery
        /// <summary>
        /// 
        /// </summary>
        /// <param name="consulta">Sentencia SQL o Store Procedure a ejecutar</param>
        /// <param name="cmdType">Tipo de comando a utilizar (sentencia o store procedure)</param>
        /// <param name="parametros">Lista de parametros que utiliza la consulta</param>
        protected static int ejecutaNonQuery(string consulta, List<SqlParameter> parametros, CommandType cmdType)
        {
            int affected;
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = GetConnectionString();
            connection.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = consulta;
            command.CommandType = cmdType;

            if (parametros != null)
            {
                command.Parameters.Clear();
                foreach (SqlParameter param in parametros)
                    command.Parameters.Add(param);
            }

            affected = command.ExecuteNonQuery();
            command.Dispose();
            if (connection.State == ConnectionState.Open)
            {
                connection.Close();
                connection.Dispose();
            }
            return affected;
        }

        public static int ejecutaNonQuery(string StoredProcedure, List<SqlParameter> parametros, SqlConnection con, SqlTransaction trn, CommandType cmdType)
        {
            int afectadas = -1;

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = StoredProcedure;
                cmd.CommandType = cmdType;
                cmd.Parameters.AddRange(parametros.ToArray());
                cmd.Transaction = trn;
                afectadas = cmd.ExecuteNonQuery();

            }
            return afectadas;
        }


        #endregion



        protected static SqlDataReader select(List<SqlParameter> parameters, string StoreProcedureoQuery, CommandType tipo)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = GetConnectionString();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = StoreProcedureoQuery;
            cmd.CommandType = tipo;

            if (parameters != null)
            {
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }
            con.Open();

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }



        protected static SqlDataReader selectAll(string StoreProcedureoQuery, CommandType tipo)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = GetConnectionString();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = StoreProcedureoQuery;
            cmd.CommandType = tipo;
            con.Open();

            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }

        #region ejecutaScalar
        public static object ejecutaScalar(string StoredProcedure, List<SqlParameter> parametros, SqlConnection con, SqlTransaction trn, CommandType cmdType)
        {
            object retorno = new object();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = StoredProcedure;
                cmd.CommandType = cmdType;
                cmd.Parameters.AddRange(parametros.ToArray());
                cmd.Transaction = trn;
                retorno = cmd.ExecuteScalar();
            }

            return retorno;
        }

        public static object ejecutaScalar(string StoredProcedure, List<SqlParameter> parametros)
        {
            object retorno = new object();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = abrirConexion();
                cmd.CommandText = StoredProcedure;
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddRange(parametros.ToArray());
                retorno = cmd.ExecuteScalar();
            }

            return retorno;
        }





        #endregion


   

        #region conexion
        public static SqlConnection abrirConexion()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = GetConnectionString();
            con.Open();
            return con;
        }

        public static void cerrarConexion(SqlConnection con)
        {
            if (con.State == ConnectionState.Open)
                con.Close();
            con.Dispose();
        }
        #endregion 
    }
}
